package org.bot.plugin.data.sqlite;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 沧月
 * @create 0017 2023/8/17 14:41
 * @description <数据库连接管理工具>
 */
public class Db {

    private static Connection connection = null;

    private static String dbPath = System.getProperty("user.dir") + "/config/data/sqlite-data.db";

    static {
        try {
            Class.forName("org.sqlite.JDBC");
            String dbURL = "jdbc:sqlite:" + dbPath;
            connection = DriverManager.getConnection(dbURL);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static PreparedStatement getPreparedStatement(String sql, Object... params) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            if (params != null && params.length > 0) {
                int index = 1;
                for (Object param : params) {
                    preparedStatement.setObject(index, param);
                    index++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }

    public static JSONArray qArray(String sql, Object... params) {
        try {
            PreparedStatement preparedStatement = getPreparedStatement(sql, params);
            ResultSet resultSet = preparedStatement.executeQuery();

            List<Map<String, Object>> resultList = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                int columnCount = resultSet.getMetaData().getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultSet.getMetaData().getColumnName(i);
                    Object columnValue = resultSet.getObject(i);
                    rowMap.put(columnName, columnValue);
                }
                resultList.add(rowMap);
            }

            // 关闭资源
            resultSet.close();
            preparedStatement.close();
            return new JSONArray(resultList);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public static JSONObject qObject(String sql, Object... params) {
        JSONArray array = qArray(sql, params);
        if (array != null && array.size() > 0) {
            return array.getJSONObject(0);
        }
        return null;
    }

    public static boolean execute(String sql, Object... params) {
        try {
            PreparedStatement preparedStatement = getPreparedStatement(sql, params);
            boolean stat = preparedStatement.execute();
            preparedStatement.close();
            return stat;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    public static void close() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
